package com.gitee.melin.bee.core.jdbc.dialect.postgres;

import com.gitee.melin.bee.core.jdbc.dialect.AbstractDBQuery;

public class PostgreSqlQuery extends AbstractDBQuery {

    private String SUPPORT_RELPARTBOUND_STATEMENT = "";

    public PostgreSqlQuery() {
    }

    public PostgreSqlQuery(String supportRrelpartbound) {
        SUPPORT_RELPARTBOUND_STATEMENT = supportRrelpartbound;
    }

    @Override
    public String tablesSql(String schemaName) {
        return String.format("SELECT n.nspname              AS schema_name\n"
                + "     , c.relname              AS tablename\n"
                + "     , obj_description(c.oid) AS comments\n"
                + "     , c.reltuples            as rows\n"
                +"      , c.oid TBLID \n"
                + "     ,  cast(c.relpages as bigint) * 8 * 1024 as data_length\n" +
                "     ,c.relkind as table_type \n"
                + "FROM pg_class c\n"
                + "         LEFT JOIN pg_namespace n ON n.oid = c.relnamespace\n"
                + "WHERE c.relkind in('r','f','p','v')"
                + " %s AND n.nspname = '"
                + schemaName
                + "'\n"
                + "ORDER BY n.nspname, tablename", SUPPORT_RELPARTBOUND_STATEMENT);
    }

    @Override
    public String columnsSql(String schemaName, String tableName) {
        return "SELECT col.column_name                              as name\n"
                + "     , col.character_maximum_length                 as length\n"
                + "     , col.is_nullable                              as is_nullable\n"
                + "     , col.numeric_precision                        as numeric_precision\n"
                + "     , col.numeric_scale                            as numeric_scale\n"
                + "     , col.ordinal_position                         as ordinal_position\n"
                + "     , col.udt_name                                 as type\n"
                + "     , (CASE  WHEN (SELECT COUNT(*) FROM pg_constraint AS PC WHERE b.attnum = PC.conkey[1] AND PC.contype = 'p' and PC.conrelid = c.oid) > 0 \n"
                + "THEN 'PRI' ELSE '' END)                            AS key\n"
                + "     , col_description(c.oid, col.ordinal_position) AS comment\n"
                + "     , col.column_default                           AS column_default\n"
                + "FROM information_schema.columns AS col\n"
                + "         LEFT JOIN pg_namespace ns ON ns.nspname = col.table_schema\n"
                + "         LEFT JOIN pg_class c ON col.table_name = c.relname AND c.relnamespace = ns.oid\n"
                + "         LEFT JOIN pg_attribute b ON b.attrelid = c.oid AND b.attname = col.column_name\n"
                + "WHERE col.table_schema = '"
                + schemaName
                + "' AND col.table_name = '"
                + tableName
                + "'\n"
                + "ORDER BY col.table_schema, col.table_name, col.ordinal_position";
    }

    @Override
    public String schemaName() {
        return "schema_name";
    }

    @Override
    public String tableName() {
        return "tablename";
    }

    @Override
    public String tableComment() {
        return "comments";
    }

    @Override
    public String rows() {
        return "rows";
    }

    @Override
    public String dataLength() {
        return "data_length";
    }

    @Override
    public String tableType() {
        return "table_type";
    }

    @Override
    public String columnName() {
        return "name";
    }

    @Override
    public String columnType() {
        return "type";
    }

    @Override
    public String columnLength() {
        return "length";
    }

    @Override
    public String columnComment() {
        return "comment";
    }

    @Override
    public String columnKey() {
        return "key";
    }

    @Override
    public String precision() {
        return "numeric_precision";
    }

    @Override
    public String scale() {
        return "numeric_scale";
    }

    @Override
    public String columnPosition() {
        return "ordinal_position";
    }

    @Override
    public String defaultValue() {
        return "column_default";
    }

    @Override
    public String isNullable() {
        return "is_nullable";
    }
}
